/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.dao.util.MySqlDataAccessHelper;
import model.pojo.Product;

/**
 *
 * @author TiOc
 */
public class ProductDAO 
{
    //Đếm số sản phẩm theo thương hiệu
    public static int countProductFollowBrandName(int idBrandName)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idBrandName = %d", idBrandName);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm số sản phẩm theo dòng sản phẩm
    public static int countProductFollowCatalogue(int idCatalogue)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idCatalogue = %d", idCatalogue);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo dòng cpu
    public static int countProductFollowCurrentCpu(String currentCpu)
    {
        int quantity = 0;
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, MicroProcessor As mp  "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idMicroProcessor = mp.idMicroProcessor And mp.currentCpu = '%s'", currentCpu); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Loc san pham theo dong cpu
    public static ArrayList<Product> FilterCurrentCpu(String currentCpu, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl, ProductDetail As pddt, MicroProcessor As mp "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idMicroProcessor = mp.idMicroProcessor And mp.currentCpu = '%s' LIMIT %d, %d", currentCpu, batDau, soSanPhamTrenTrang); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
    //Đếm sản phẩm theo toc do cpu
    public static int countProductFollowCpu(double sizeCpu)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, MicroProcessor As mp "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idMicroProcessor = mp.idMicroProcessor And mp.sizeCpu >= %f", sizeCpu);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Loc san pham theo toc do cpu
    public static ArrayList<Product> FilterSizeCpu(double sizeCpu, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, MicroProcessor As mp "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idMicroProcessor = mp.idMicroProcessor And mp.sizeCpu >= %f LIMIT %d, %d", sizeCpu, batDau, soSanPhamTrenTrang);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
    //Đếm sản phẩm theo ram
    public static int countProductFollowRam(int sizeRam)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Ram As r "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idRam =  r.idRam And r.sizeRam >= %d", sizeRam);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Loc san pham theo ram
    public static ArrayList<Product> FilterRam(int sizeRam, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Ram As r "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idRam =  r.idRam And r.sizeRam >= %d LIMIT %d, %d", sizeRam, batDau, soSanPhamTrenTrang);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
    //Đếm sản phẩm theo hdd
    public static int countProductFollowHdd(int sizeHdd)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Hdd As h "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idHdd =  h.idHdd And h.sizeHdd >= %d", sizeHdd);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Loc san pham theo hdd
    public static ArrayList<Product> FilterHdd(int sizeHdd, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Hdd As h "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idHdd =  h.idHdd And h.sizeHdd >= %d LIMIT %d, %d", sizeHdd, batDau, soSanPhamTrenTrang);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
    //Đếm sản phẩm theo display
    public static int countProductFollowDisplay(double sizeDisplay)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Display As d "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idDisplay = d.idDisplay And d.sizeDisplay >= %f", sizeDisplay);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Loc san pham theo display co phan trang
    public static ArrayList<Product> FilterDispaly(double sizeDisplay, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Display As d "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idDisplay = d.idDisplay And d.sizeDisplay >= %f LIMIT %d, %d", sizeDisplay, batDau, soSanPhamTrenTrang);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
    //Đếm sản phẩm theo Battery
    public static int countProductFollowCell(int sizeCell)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Battery As bt "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idBattery = bt.idBattery And bt.sizeBattery >= %d", sizeCell); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Loc san pham theo battery co phan trang
    public static ArrayList<Product> FilterBattery(int sizeCell, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl,  ProductDetail As pddt, Battery As bt "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = pddt.idProduct And pddt.idBattery = bt.idBattery And bt.sizeBattery >= %d LIMIT %d, %d", sizeCell, batDau, soSanPhamTrenTrang); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
    //Lay san pham theo idProduct
     public static Product getProductFollowId(int idProduct)
    {
        Product pd = new Product();
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = %d", idProduct); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setIdCatalogue(rs.getInt("idCatalogue"));
                pd.setNameCatalogue(rs.getString("nameCatalogue"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("Price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return pd;
    }
     
     //Lay san pham theo catalogue co phan trang
     public static ArrayList<Product> getProductFollowCatalogue(int idCatalogue, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idCatalogue = %d ORDER BY pd.nameProduct LIMIT %d, %d", idCatalogue, batDau, soSanPhamTrenTrang);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setIdCatalogue(rs.getInt("idCatalogue"));
                pd.setNameCatalogue(rs.getString("nameCatalogue"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("Price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
    
     //Lay san pham noi bat
     public static ArrayList<Product> laySanPhamNoiBat()
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(4);
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' ORDER BY viewer DESC LIMIT %d, %d", 0, soSanPhamTrenTrang);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setIdCatalogue(rs.getInt("idCatalogue"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("Price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return ds;
    }
     
    public static int laySoLuongSanPham()
    {
        String sql = String.format("SELECT COUNT(*) As SoLuong FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1'");
        int soLuong = -1;
        try
        {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                soLuong = rs.getInt("SoLuong");
            }
            helper.close();
        }
        catch(SQLException ex)
        {
            System.out.print(ex.getMessage());
        }
        return soLuong;
    }
    
    public static int tinhSoLuongTrang()
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.laySoLuongSanPham();
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    //Tinh so luong trang theo dong cpu
    public static int tinhSoLuongTrangCurrentCpu(String nameCurrentCpu)
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.countProductFollowCurrentCpu(nameCurrentCpu);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    //Tinh so luong trang theo toc do cpu
    public static int tinhSoLuongTrangSpeedCpu(double sizeCpu)
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.countProductFollowCpu(sizeCpu);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    //Tinh so luong trang theo ram
    public static int tinhSoLuongTrangRam(int sizeRam)
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.countProductFollowRam(sizeRam);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    //Tinh so luong trang theo hdd
    public static int tinhSoLuongTrangHdd(int sizeHdd)
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.countProductFollowHdd(sizeHdd);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    //Tinh so luong trang theo display
    public static int tinhSoLuongTrangDisplay(double sizeDisplay)
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.countProductFollowDisplay(sizeDisplay);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    //Tinh so luong trang theo Battery
    public static int tinhSoLuongTrangBattery(int sizeCell)
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.countProductFollowCell(sizeCell);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    public static ArrayList<Product> layDanhSachSanPhamCoPhanTrang(int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' LIMIT %d, %d", batDau, soSanPhamTrenTrang);
        try
        {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql); 
           
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
            helper.close();
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
   
        return ds;
    }
    
    //Phan trang san pham theo catalogue
    public static ArrayList<Product> layDanhSachSanPhamTheoCataloguePhanTrang(int idCatalogue, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idCatalogue = %d LIMIT %d, %d", idCatalogue, batDau, soSanPhamTrenTrang);
        try
        {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql); 
           
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
            helper.close();
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
   
        return ds;
    }
    
    //Phan trang san pham theo brandname
    public static ArrayList<Product> layDanhSachSanPhamTheoBrandNameCoPhanTrang(int idBrandName, int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idBrandName = %d LIMIT %d, %d", idBrandName, batDau, soSanPhamTrenTrang);
        try
        {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql); 
           
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setQuantity(rs.getInt("quantity"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                pd.setViewer(rs.getInt("viewer"));
                ds.add(pd);
            }
            helper.close();
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
   
        return ds;
    }
    
    //Lay luot nguoi xem san pham
    public static int getViewer(int idProduct)
    {
        int viewer = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT viewer FROM Product As pd, BrandName As bn, Catalogue As cl "
                + "WHERE pd.isPublish = '1' And pd.idBrandName = bn.idBrandName And bn.isPublish = '1' And pd.idCatalogue = cl.idCatalogue And cl.isPublish = '1' And pd.idProduct = %d", idProduct);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                viewer = rs.getInt("viewer");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return viewer;
    }
    
    //Cap nhat luot xem
    public static boolean updateViewer(int idProduct, int viewer)
    {
        boolean kq = false;
        String sql = String.format("UPDATE Product SET viewer = %d WHERE idProduct = %d", viewer, idProduct);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql); 
        if(n != 0)
        {
            kq = true;
        }
        helper.close();
        
        return kq;
    }
}
